/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Controler;

import Model.Country;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Maria
 */
public class CountryDAPImp {

    private PreparedStatement saveStmt;
    private PreparedStatement loadStmt;
    private PreparedStatement deleteStmt;
private PreparedStatement importStmt;
    private PreparedStatement exportStmt;
    
    public CountryDAPImp(String url, String user, String pw) {
        try {
            Connection con = DriverManager.getConnection(url, user, pw);
            saveStmt = con.prepareStatement("INSERT INTO ex_country(  name, export, import) "
                    + "VALUES (?, ?, ?)");
            loadStmt = con.prepareStatement("SELECT name FROM ex_country ");

            deleteStmt = con.prepareStatement("DELETE FROM ex_country WHERE name = ?");

        } catch (SQLException e) {
            System.out.println("Connection Failed ");
        }
    }

    public CountryDAPImp(Connection con) {
        //throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        try {
            
            saveStmt = con.prepareStatement("INSERT INTO ex_country(  name, export, import) "
                    + "VALUES (?, ?, ?)");
            loadStmt = con.prepareStatement("SELECT name FROM ex_country ");

            deleteStmt = con.prepareStatement("DELETE FROM ex_country WHERE name = ?");
            
            importStmt = con.prepareStatement("SELECT name FROM ex_country WHERE import='Y'  ");
            
            exportStmt = con.prepareStatement("SELECT name FROM ex_country WHERE export='Y'");

        } catch (SQLException e) {
            System.out.println("Connection Failed ");
        }
    }

    public List<Country> getAll() {
        List<Country> allC = new ArrayList<Country>();

        try {
            ResultSet result = loadStmt.executeQuery();
            while (result.next()) {
                Country country = new Country();

                country.setName(result.getString("NAME"));
                country.setName(result.getString("EXPORT"));
                country.setName(result.getString("IMPORT"));

                allC.add(country);
            }
        } catch (SQLException e) {
        }
        return allC;
    }

    public boolean addCountry(String name, String exp, String imp) {
        System.out.println("entra add ");
           System.out.println(saveStmt);
        try {
            saveStmt.setString(1, name);
            saveStmt.setString(2, exp);
            saveStmt.setString(3, imp);
            System.out.println(saveStmt);
            saveStmt.execute();
            return true;
        } catch (SQLException e) {
            System.out.println("Error insert country");
            return false;
        }
    }

    public boolean deleteCountry(Country country) {

        try {
            deleteStmt.setString(1, country.getName());

            deleteStmt.execute();

            return true;
        } catch (SQLException e) {
            System.out.println("Error insert country");
            return false;
        }
    }

    public List<String> getImp()
    {
        List<String> allC = new ArrayList<String>();

        try {
            ResultSet result = importStmt.executeQuery();
            while (result.next()) {
                allC.add(result.getString("name"));
               // Country country = new Country();

                //country.setName(result.getString("NAME"));
                //country.setName(result.getString("EXPORT"));
                //country.setName(result.getString("IMPORT"));

                //allC.add(country);
            }
        } catch (SQLException e) {
        }
        return allC;
    }
     public List<String> getExp()
     {
         List<String> allC = new ArrayList<String>();

        try {
            ResultSet result = exportStmt.executeQuery();
            while (result.next()) {
                //Country country = new Country();

                allC.add(result.getString("name"));
                //country.setName(result.getString("EXPORT"));
                //country.setName(result.getString("IMPORT"));

               // allC.add(country);
            }
        } catch (SQLException e) {
        }
        return allC;
     }
    
}
